import logging

import pymysql

from conf import config

logger = logging.getLogger("wechat.helper.mysql_help")


def get_main_connection():
    """
    连接主数据库
    :return:
    """
    conn = pymysql.connect(
        host=config.MYSQL_HOST,
        port=config.MYSQL_PORT,
        user=config.MYSQL_USER,
        passwd=config.MYSQL_PWD,
        db=config.MYSQL_DB,
        charset='utf8',
        cursorclass=pymysql.cursors.DictCursor
    )
    return conn


def close_connection(conn):
    """
    关闭数据库连接
    :param conn:
    :return:
    """
    conn.close()


def get_store_info(lng=0, lat=0, skip=0, limit=10, content=None, pick_up=0, sub_type=0):
    """
    获取门店信息
    :param content:
    :return:
    """
    conn = get_main_connection()
    args = []
    try:
        cursor = conn.cursor()
        if sub_type:
            # 高级门店=1 行家=2 运营中心=3
            if lng and lat:
                # 前端定位成功
                args.extend([lat, lat, lng])
                sql = 'SELECT g.ebf_agency_id id, g.ebf_agency_name name, g.ebf_agency_contact phone, ' \
                      'g.ebf_agency_address address, g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl, ' \
                      'ROUND(6378138 * 2 * ASIN(SQRT(POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_latitude * PI() / 180) / 2), 2) + COS(%s * PI() / 180) * ' \
                      'COS(g.ebf_agency_latitude * PI() / 180) * POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_longitude * PI() / 180 ) / 2), 2))), 2) distance ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'WHERE g.ebf_agency_type=3 AND g.ebf_agency_sub_type=' + str(sub_type) + ' '
            else:
                # 前端定位失败
                sql = 'SELECT g.ebf_agency_id id, g.ebf_agency_name name, g.ebf_agency_contact phone, ' \
                      'g.ebf_agency_address address, g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'WHERE g.ebf_agency_type=3 AND g.ebf_agency_sub_type=' + str(sub_type) + ' '

        elif pick_up == 1:
            # 礼品订单门店自提 具有V款且库存不小于2的门店按距离由近及远排序
            goods_id = "562897119687463"
            if lng and lat:
                # 前端定位成功
                args.extend([lat, lat, lng])
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl, ' \
                      'ROUND(6378138 * 2 * ASIN(SQRT(POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_latitude * PI() / 180) / 2), 2) + COS(%s * PI() / 180) * ' \
                      'COS(g.ebf_agency_latitude * PI() / 180) * POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_longitude * PI() / 180 ) / 2), 2))), 2) distance ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'AND s.sdef_goods_count>=2 WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            else:
                # 前端定位失败
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'AND s.sdef_goods_count>=2 WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            args.append(goods_id)
        elif pick_up == 2:
            # 买单送I款的门店 即具有买单送I款商品的门店按距离由近及远排序
            goods_id = "565766647414853"
            if lng and lat:
                # 前端定位成功
                args.extend([lat, lat, lng])
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl, ' \
                      'ROUND(6378138 * 2 * ASIN(SQRT(POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_latitude * PI() / 180) / 2), 2) + COS(%s * PI() / 180) * ' \
                      'COS(g.ebf_agency_latitude * PI() / 180) * POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_longitude * PI() / 180 ) / 2), 2))), 2) distance ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            else:
                # 前端定位失败
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            args.append(goods_id)
        elif pick_up == 3:
            # 具有酒杯酒且库存不小于1的门店按距离由近及远排序
            goods_id = "568105146613081"
            if lng and lat:
                # 前端定位成功
                args.extend([lat, lat, lng])
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl, ' \
                      'ROUND(6378138 * 2 * ASIN(SQRT(POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_latitude * PI() / 180) / 2), 2) + COS(%s * PI() / 180) * ' \
                      'COS(g.ebf_agency_latitude * PI() / 180) * POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_longitude * PI() / 180 ) / 2), 2))), 2) distance ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'AND s.sdef_goods_count>=1 WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            else:
                # 前端定位失败
                sql = 'SELECT g.ebf_agency_id id, s.sdef_goods_id goods_id, s.sdef_goods_count goods_count, ' \
                      'g.ebf_agency_name name, g.ebf_agency_contact phone, g.ebf_agency_address address, ' \
                      'g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'LEFT JOIN sdet_store_goods s on s.sdef_goods_store_id=g.ebf_agency_id AND s.sdef_goods_id=%s ' \
                      'AND s.sdef_goods_count>=1 WHERE g.ebf_agency_type=3 AND s.sdef_goods_id IS NOT NULL '
            args.append(goods_id)
        else:
            # 店内点酒
            if lng and lat:
                # 前端定位成功
                args.extend([lat, lat, lng])
                sql = 'SELECT g.ebf_agency_id id, g.ebf_agency_name name, g.ebf_agency_contact phone, ' \
                      'g.ebf_agency_address address, g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl, ' \
                      'ROUND(6378138 * 2 * ASIN(SQRT(POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_latitude * PI() / 180) / 2), 2) + COS(%s * PI() / 180) * ' \
                      'COS(g.ebf_agency_latitude * PI() / 180) * POW(SIN((%s * PI() / 180 ' \
                      '- g.ebf_agency_longitude * PI() / 180 ) / 2), 2))), 2) distance ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'WHERE g.ebf_agency_type=3 '
            else:
                # 前端定位失败
                sql = 'SELECT g.ebf_agency_id id, g.ebf_agency_name name, g.ebf_agency_contact phone, ' \
                      'g.ebf_agency_address address, g.ebf_agency_work_time work_time, g.ebf_agency_is_active active, ' \
                      'g.ebf_agency_longitude lng, g.ebf_agency_latitude lat, u.ebf_user_headurl headurl ' \
                      'FROM ebt_agency g LEFT JOIN ebt_user u ON concat("P", g.ebf_agency_id)=u.ebf_user_id ' \
                      'WHERE g.ebf_agency_type=3 '

        if content:
            sql += 'AND ebf_agency_name LIKE %s '
            args.append('%'+ content + '%')

        if lng and lat:
            sql += 'ORDER BY distance LIMIT %s, %s'
        else:
            sql += 'ORDER BY ebf_agency_id LIMIT %s, %s'
        args.append(skip)
        args.append(limit)
        count = cursor.execute(sql, args)
        result = cursor.fetchall()
        logger.info(f"count={count}")
    except Exception as e:
        logger.error(e)
        result = []
    finally:
        cursor.close()
        close_connection(conn)
        return result